﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Entidad;
using System.Configuration;
namespace Dao
{
    public class RendimientoDao
    {
        private static SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["AcademicoConnectionString"].ConnectionString);
        public static SqlCommand cmd;
        public static SqlTransaction tran;
        public static int cant;
        public static int sumatoria;
        public static float promedio;
        public RendimientoDao()
        {
            tran = null;
        }
        internal static void ActualizarEstados(int codAsi, SqlConnection cn, SqlTransaction tran)
        {
           
            string sql = @"update InscripcionXAlumno set codEstado=2 where codAsignatura=@CodAsignatura";
            cmd = new SqlCommand(sql, cn, tran);
            cmd.Parameters.Add("@CodAsignatura", SqlDbType.Int).Value = codAsi;
            cmd.ExecuteNonQuery();
        }
        internal static void CalcularPromedio(int codAsi, SqlConnection cn, SqlTransaction tran)
        {
            cant=0;
            sumatoria=0;
            string sql = "Select nota From NotaParciales Where codAsignatura=@CodAsignatura";
            cmd = new SqlCommand(sql, cn, tran);
            cmd.Parameters.Add("@CodAsignatura", SqlDbType.Int).Value = codAsi;
            SqlDataReader dr = cmd.ExecuteReader();
            while(dr.Read()){
                cant++;
                sumatoria += (int) dr[0];
            }
            dr.Close();
            promedio=(float)(sumatoria/cant);
        }
        internal static void ActualizarAsignatura(int codAsi, SqlConnection cn, SqlTransaction tran)
        {
           
            string sql = @"update Asignatura set promedio=@Promedio, fechaCierre=@FechaCierre where codAsignatura=@CodAsignatura";
            cmd = new SqlCommand(sql, cn, tran);
            cmd.Parameters.Add("@CodAsignatura", SqlDbType.Int).Value = codAsi;
            cmd.Parameters.Add("@Promedio", SqlDbType.Decimal).Value = promedio;
            cmd.Parameters.Add("@FechaCierre", SqlDbType.Date).Value = DateTime.Now.Date;
            cmd.ExecuteNonQuery();
        }
        
        public static float Insertar(int codAsi)
        {

            try
            {
                if (cn.State != ConnectionState.Open)
                cn.Open();
                tran = cn.BeginTransaction();

                     //Actualizar los estados de inscripcion
                    ActualizarEstados(codAsi,cn, tran);
                    //Obtener en una lista todas las notas
                    CalcularPromedio(codAsi,cn,tran);
                    //Actualizar el promedio y la fecha en Asignatura
                    ActualizarAsignatura(codAsi,cn,tran);
                tran.Commit();
                cn.Close();
            }
            catch (SqlException ex)
            {
                if (cn.State == ConnectionState.Open)
                {
                    tran.Rollback();
                    cn.Close();
                }
                throw new ApplicationException("Error al insertar la Asignatura para el docente. \n Problema:" + ex.Message);
            }
            return promedio;
        }

         public static List<Asignatura> CargarDDLAsignaturas(int legajo)
        {
            List<Asignatura> listAsignatura = new List<Asignatura>();
            string sql = @"SELECT a.codAsignatura, m.nombre, c.numAula, a.turno
                            FROM Asignatura AS a INNER JOIN
                            Curso AS c ON a.codCurso = c.codCurso INNER JOIN
                            Materia AS m ON a.codMateria = m.codMateria
                            WHERE a.legDocente = @Legajo AND a.promedio = 0 ORDER BY m.nombre";
            SqlCommand cmd = new SqlCommand(sql, cn);
            cmd.Parameters.Add("@Legajo", SqlDbType.Int).Value = legajo;
            try
            {
                if (cn.State != ConnectionState.Open)
                cn.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Asignatura asi = new Asignatura();
                    asi.codAsignatura = (int)dr[0];
                    asi.nombreMateria = dr[1].ToString() + " - Aula " + dr[2].ToString() + " - " + dr[3].ToString();
                    
                    listAsignatura.Add(asi);
                }
                dr.Close();
                cn.Close();
            }
            catch (SqlException ex)
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                throw new ApplicationException("Error BD Asignatura \n Problema:" + ex.Message);
            }
            return listAsignatura;

        }
         public static bool ValidarInscripRegistradas(int codAsignatura)
         {
             bool valido = false;
             string sql = @"SELECT ixa.codInscripcion From InscripcionXAlumno ixa
                            Inner Join Asignatura a on ixa.codAsignatura=a.codAsignatura
                            Where a.codAsignatura=@CodAsignatura";
             SqlCommand cmd = new SqlCommand(sql, cn);
             cmd.Parameters.Add("@CodAsignatura", SqlDbType.Int).Value = codAsignatura;
             try
             {
                 if (cn.State != ConnectionState.Open)
                 cn.Open();
                 SqlDataReader dr = cmd.ExecuteReader();
                 while (dr.Read())
                 {
                     valido = true;
                 }
                 dr.Close();
                 cn.Close();
                 
             }
             catch (SqlException ex)
             {
                 if (cn.State == ConnectionState.Open)
                     cn.Close();
                 throw new ApplicationException("Error BD InscripcionXAlumno, Asignatura \n Problema:" + ex.Message);
             }
             return valido;
         }
         public static bool ValidarNotasCargadas(int codAsignatura)
         {
             bool valido = false;
             string sql = @"SELECT np.legajo
                            FROM NotaParciales AS np INNER JOIN
                            Asignatura AS a ON np.codAsignatura = a.codAsignatura
                            WHERE a.codAsignatura = @CodAsignatura";
             SqlCommand cmd = new SqlCommand(sql, cn);
             cmd.Parameters.Add("@CodAsignatura", SqlDbType.Int).Value = codAsignatura;
             try
             {
                 if (cn.State != ConnectionState.Open)
                     cn.Open();
                 SqlDataReader dr = cmd.ExecuteReader();
                 while (dr.Read())
                 {
                     valido = true;
                 }
                 dr.Close();
                 cn.Close();
             }
             catch (SqlException ex)
             {
                 if (cn.State == ConnectionState.Open)
                     cn.Close();
                 throw new ApplicationException("Error BD NotaParciales, Asignatura \n Problema:" + ex.Message);
             }
             return valido;
         }
      
    }

}
